• Home
 · 
• Math/Dates
 · 
• Function Takes a Number and Returns the Text for that Number. Example 123.50 => One hundred twenty

Function Takes a Number and Returns the Text for that Number. Example 123.50 => One hundred twenty

Function Takes a Number and Returns the Text for that Number. Example 123.50 => One hundred twenty three and 50/100

Rate Function Takes a Number and Returns the Text for that Number. Example 123.50 => One hundred twenty

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

    Static ones(0 To 9) As String
    Static teens(0 To 9) As String
    Static tens(0 To 9) As String
    Static thousands(0 To 4) As String
    Dim i As Integer, nPosition As Integer
    Dim nDigit As Integer, bAllZeros As Integer
    Dim strResult As String, strTemp As String
    Dim tmpBuff As String

    ones(0) = "Zero"
    ones(1) = "One"
    ones(2) = "Two"
    ones(3) = "Three"
    ones(4) = "Four"
    ones(5) = "Five"
    ones(6) = "Six"
    ones(7) = "Seven"
    ones(8) = "Eight"
    ones(9) = "Nine"

    teens(0) = "Ten"
    teens(1) = "Eleven"
    teens(2) = "Twelve"
    teens(3) = "Thirteen"
    teens(4) = "Fourteen"
    teens(5) = "Fifteen"
    teens(6) = "Sixteen"
    teens(7) = "Seventeen"
    teens(8) = "Eighteen"
    teens(9) = "Nineteen"

    tens(0) = ""
    tens(1) = "Ten"
    tens(2) = "Twenty"
    tens(3) = "Thirty"
    tens(4) = "Forty"
    tens(5) = "Fifty"
    tens(6) = "Sixty"
    tens(7) = "Seventy"
    tens(8) = "Eighty"
    tens(9) = "Ninty"

    thousands(0) = ""
    thousands(1) = "Thousand"
    thousands(2) = "Million"
    thousands(3) = "Billion"
    thousands(4) = "Trillion"

    'Trap errors
    On Error GoTo NumToTextError
    'Get fractional part
    strResult = "and " & Format((no1 - Int(no1)) * 100, "00") & "/100"
    'Convert rest to string and process each digit
    strTemp = CStr(Int(no1))
    'Iterate through string
    For i = Len(strTemp) To 1 Step -1
        'Get value of this digit
        nDigit = Val(Mid$(strTemp, i, 1))
        'Get column position
        nPosition = (Len(strTemp) - i) + 1
        'Action depends on 1's, 10's or 100's column
        Select Case (nPosition Mod 3)
            Case 1  '1's position
                bAllZeros = False
                If i = 1 Then
                    tmpBuff = ones(nDigit) & " "
                ElseIf Mid$(strTemp, i - 1, 1) = "1" Then
                    tmpBuff = teens(nDigit) & " "
                    i = i - 1   'Skip tens position
                ElseIf nDigit > 0 Then
                    tmpBuff = ones(nDigit) & " "
                Else
                    'If next 10s & 100s columns are also
                    'zero, then don't show 'thousands'
                    bAllZeros = True
                    If i > 1 Then
                        If Mid$(strTemp, i - 1, 1) <> "0" Then
                            bAllZeros = False
                        End If
                    End If
                    If i > 2 Then
                        If Mid$(strTemp, i - 2, 1) <> "0" Then
                            bAllZeros = False
                        End If
                    End If
                    tmpBuff = ""
                End If
                If bAllZeros = False And nPosition > 1 Then
                    tmpBuff = tmpBuff & thousands(nPosition / 3) & " "
                End If
                strResult = tmpBuff & strResult
            Case 2  'Tens position
                If nDigit > 0 Then
                    strResult = tens(nDigit) & " " & strResult
                End If
            Case 0  'Hundreds position
                If nDigit > 0 Then
                    strResult = ones(nDigit) & " hundred " & strResult
                End If
        End Select
    Next i
    'Convert first letter to upper case
    If Len(strResult) > 0 Then
        strResult = UCase$(Left$(strResult, 1)) & Mid$(strResult, 2)
    End If

EndNumToText:
    'Return result
    NumToText = strResult
    Exit Function

NumToTextError:
    strResult = "#Error#"
    Resume EndNumToText
End Function